﻿#!/usr/bin/env python3

import logging, os.path, paramiko, time, re, sqlite3
from datetime import datetime

# 1.创建一个logger
logger = logging.getLogger()
logger.setLevel(logging.INFO)  # Log等级总开关,默认level是WARNING,低于该级别的就不输出了,级别排序: CRITICAL > ERROR > WARNING > INFO > DEBUG

# 2.创建一个handler，用于写入日志文件
"""
time.time():从1970年1月1日0分0时0秒起至当前时间，经过的浮点数
time.localtime()：转换为time.struct_time类型的对象的秒数
time.strftime() 函数根据指定的格式把一个时间字符串解析为时间元组
"""
log_time = time.strftime('%Y_%m_%d-%H_%M_%S', time.localtime(time.time()))
log_path = os.path.dirname(os.getcwd())   # os.getcwd() 用于返回当前工作目录
log_name = log_path + log_time + '.log'
log_file = log_name
fh = logging.FileHandler(log_file, mode='w')
fh.setLevel(logging.INFO)  # 输出到file的log等级的开关
# output to console
ch = logging.StreamHandler()
ch.setLevel(logging.WARNING)

# 3.定义handler的输出格式
formatter = logging.Formatter("%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s")
fh.setFormatter(formatter)

# 4.将logger添加到handler里面
logger.addHandler(fh)
# output to console
ch.setFormatter(formatter)
logger.addHandler(ch)

def sqlite3_h3c_ssh2_log(ip, port, username, password, cmd_list, enable='', verbose=True):
    try:
        ssh = paramiko.SSHClient()
        ssh.load_system_host_keys()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(ip, port, username, password, timeout=3, compress=True)
        chan = ssh.invoke_shell()
        time.sleep(1)
        x = chan.recv(2048).decode()
        logging.warning('%s connect success ' % ip)

        if enable and '>' in x:
            chan.send('enable'.encode() + b'\n')
            time.sleep(1)
            chan.send(enable.encode() + b'\n')
            time.sleep(1)
        elif not enable and '>' in x:
            print('need enable password')
            return


        date_timer = datetime.now().strftime('%Y_%m_%d')
        sqlite3_str = 'sqlite3-h3c-' + date_timer + '-db.sqlite3'

        print('=== SQLite3 init... ===')
        time.sleep(1)
        try:
            conn = sqlite3.connect(sqlite3_str)
            print(f'Connect {sqlite3_str} successfully')
            cur = conn.cursor()

            cur.execute('''
            CREATE TABLE sw_h3c_main_tb (
              ipaddr VARCHAR(15) NOT NULL,
              sysname VARCHAR(64) NOT NULL DEFAULT 'null',
              model VARCHAR(24) NOT NULL DEFAULT 'null',
              sn VARCHAR(24) NOT NULL DEFAULT 'null',
              mac VARCHAR(14) NOT NULL DEFAULT 'null',
              date VARCHAR(10) NOT NULL DEFAULT 'null',
              PRIMARY KEY (ipaddr)
            );
            ''')
            conn.commit()
            print(f'The sw_h3c_main_tb of {sqlite3_str} created successfully')

            cur.execute('''
            CREATE TABLE sw_h3c_if_tb (
              interface VARCHAR(21) NOT NULL DEFAULT 'null',
              type VARCHAR(1) NOT NULL DEFAULT 'null',
              pvid VARCHAR(4) NOT NULL DEFAULT '0000',
              host VARCHAR(15) NOT NULL,
              FOREIGN KEY (host) REFERENCES sw_h3c_main_tb (ipaddr) ON DELETE CASCADE ON UPDATE CASCADE
            );
            ''')
            conn.commit()
            print(f'The sw_h3c_if_tb of {sqlite3_str} created successfully')

        except Exception as e:
            print(f'Creating {sqlite3_str} failed ', e)


        # cisco and h3c diff
        chan.send('undo terminal monitor'.encode() + b'\n')
        time.sleep(1)
        chan.send('screen-length disable'.encode() + b'\n')
        time.sleep(1)


        print(f'loading {ip} info......')
        ### SW主机名
        chan.send('display current-configuration | include sysname'.encode() + b'\n')
        time.sleep(4)
        x = chan.recv(40960).decode()
        if verbose:
            # print(x)
            sysname_str = re.findall(r' sysname (\S+)', x)[0]
            print(f'{ip} sysname is:', sysname_str)

            ### SW版本信息等等
            chan.send('display device manuinfo'.encode() + b'\n')
            time.sleep(3)
            x = chan.recv(40960).decode()
            if verbose:
                # print(x)
                model_str = re.findall(r'DEVICE_NAME\s+:\s+(\S+)', x)[0]
                print(f'{ip} model is:', model_str)
                sn_str = re.findall(r'DEVICE_SERIAL_NUMBER\s+:\s+(\S+)', x)[0]
                print(f'{ip} sn is:', sn_str)
                mac_str = re.findall(r'MAC_ADDRESS\s+:\s+(\S+)', x)[0]
                print(f'{ip} mac is:', mac_str)
                date_str = re.findall(r'MANUFACTURING_DATE\s+:\s+(\S+)', x)[0]
                print(f'{ip} date is:', date_str)

                print('=== SQLite3 insert sw_h3c_main_tb... ===')
                time.sleep(1)
                try:
                    conn = sqlite3.connect(sqlite3_str)
                    print(f'Connect {sqlite3_str} successfully')
                    cur = conn.cursor()
                    cur.execute(
                        "INSERT INTO sw_h3c_main_tb (ipaddr, sysname, model, sn, mac, date) VALUES ('%s','%s','%s','%s','%s','%s')" %
                        (ip, sysname_str, model_str, sn_str, mac_str, date_str)
                    )
                    conn.commit()
                    print(f'Insert the sw_h3c_main_tb of {sqlite3_str} successfully')

                except Exception as e:
                    print(f'Failed to insert sw_h3c_main_tb of {sqlite3_str} ', e)

        ### SW接口信息
        chan.send('display interface brief'.encode() + b'\n')
        time.sleep(3)
        x = chan.recv(40960).decode()
        if verbose:
            ifs_strs = re.findall(r'(\S+)\s+(UP|DOWN|ADM)\s+(\S+)\s+(\S+)\s+([A|T])\s+(\d+)\s.*', x)
            # print(ifs_strs)
            for i in ifs_strs:
                # print(i[0]) 
                # print(i[4]) 
                # print(i[5]) 

                print('=== SQLite3 insert sw_h3c_if_tb... ===')
                try:
                    conn = sqlite3.connect(sqlite3_str)
                    print(f'Connect {sqlite3_str} successfully')
                    cur = conn.cursor()
                    cur.execute(
                        "INSERT INTO sw_h3c_if_tb (interface, type, pvid, host) VALUES ('%s','%s','%s','%s')" %
                        (i[0], i[4], i[5], ip)
                    )
                    conn.commit()
                    print(f'Insert the sw_h3c_if_tb of {sqlite3_str} successfully')

                    conn.close()

                except Exception as e:
                    print(f'Failed to insert sw_h3c_if_tb of {sqlite3_str} ', e)
                ### === SQLite3 end ===

        chan.close()
        ssh.close()
        logging.warning(f'The job of {ip} is over, start next...')

    except Exception as e:
        print(f'{ip} connect failed ', e)
        logging.warning('%s connect failed ' % ip)

if __name__ == '__main__':
    import all_authen
    username = all_authen.login_user
    password = all_authen.login_token

    ip_list = open('sqlite3_ips.txt', 'r')
    for ip in ip_list:
        sqlite3_h3c_ssh2_log(ip.strip(), 22, username, password, ['dis clock'], enable='cisco', verbose=True)
